const express = require('express')
const mysql = require('mysql')

const zxApp = express();
zxApp.use(express.json())  /* 请求数据以json格式为主 */
zxApp.use(express.urlencoded({ extended: true })); /* 请求头的数据类型为 x/www-form-urlencoded */

// 配置数据库访问方式
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'k3project'
})

// 渲染
// zxApp.get('/zx_query', function (req, res) {
//     let page = req.query.page
//     let current = req.query.current
//     const mysql = `SELECT * FROM ceshi LIMIT ${(page - 1) * current},${current}`
//     const mysql2 = `select COUNT(*) as total from ceshi`
//     const mysql3 = `SELECT * FROM ceshi WHERE 商品名 LIKE '%${req.query.mohu}%'`
//     db.query(mysql, function (err, data) {
//         if (err) {
//             console.log(err);
//             res.send({ code: 1, msg: err })
//         } else {
//             db.query(mysql2, function (err2, data2) {
//                 if (err2) {
//                     console.log(err2);
//                     res.send({ error: 1, msg: '失败' })
//                 } else {
//                     console.log(data2);
//                     res.send({ error: 0, msg: '成功', data: data, total: data2[0].total })
//                 }
//             })

//         }
//     })
// })

// 渲染主页面
zxApp.get('/zx_query', function (req, res) {
    let page = req.query.page
    let current = req.query.current
    if (req.query.abb_s1 == '全部') {
        req.query.abb_s1 = '%%'
    }

    if (req.query.abb_s2 == '全部') {
        req.query.abb_s2 = '%%'
    }
    // const mysql = [`SELECT * FROM ceshi LIMIT ${(page - 1) * current},${current}`, `SELECT * FROM ceshi WHERE 商品名 LIKE '%${req.query.mohu}%'`]
    // const mysql = [`SELECT * FROM (SELECT * FROM ceshi WHERE 商品名 LIKE '%${req.query.mohu}%')as d1 LIMIT ${(page - 1) * current},${current}`,
    // `SELECT * FROM ceshi WHERE 商品名 LIKE '%${req.query.mohu}%'`]
    // const mysql = `SELECT * FROM (SELECT * FROM z_xiaoshou WHERE 商品名 LIKE '%${req.query.mohu}%')as d1 LIMIT ${(page - 1) * current},${current}`
    // const mysql = `SELECT * FROM (select * from z_xiaoshou WHERE 商品名 LIKE '%${req.query.mohu}%' and  销售点 LIKE '${req.query.abb_s1}' and 销售方式 LIKE '${req.query.abb_s2}')as d1 LIMIT ${(page - 1) * current},${current}`
    // const mysql2 = `select COUNT(*) as total from z_xiaoshou WHERE 商品名 LIKE '%${req.query.mohu}%'  and  销售点 LIKE '${req.query.abb_s1}' and 销售方式 LIKE '${req.query.abb_s2}'`

    const mysql = `SELECT * FROM (SELECT a.*,c.c2_name FROM z_sale a INNER JOIN production b ON a.p_id = b.p_id
        LEFT JOIN class2 c on  b.p_name_id=c.c2_id
        WHERE 销售地点  LIKE '%${req.query.abb_s1}%' AND c2_name LIKE '%${req.query.mohu}%' AND 销售方式 LIKE '%${req.query.abb_s2}%'
        )as d2
        LIMIT ${(page - 1) * current},${current}`

    const mysql2 = `SELECT * FROM (SELECT COUNT(*)as total FROM z_sale a INNER JOIN production b ON a.p_id = b.p_id
        LEFT JOIN class2 c on  b.p_name_id=c.c2_id
        WHERE 销售地点  LIKE '%${req.query.abb_s1}%' AND c2_name LIKE '%${req.query.mohu}%' AND 销售方式 LIKE '%${req.query.abb_s2}%'
        )as d2`

    // const mysql2 = [`select COUNT(*) as total from ceshi`, `select COUNT(*) as total2 from ceshi WHERE 商品名 LIKE '%${req.query.mohu}%'`]
    // const mysql3 = `SELECT * FROM ceshi WHERE 商品名 LIKE '%${req.query.mohu}%'`
    // const mysql4 = `select COUNT(*) as total2 from ceshi WHERE 商品名 LIKE '%${req.query.mohu}%'`
    db.query(mysql, function (err, data) {
        if (err) {
            console.log(err);
            res.send({ code: 1, msg: err })
        } else {
            db.query(mysql2, function (err2, data2) {
                if (err2) {
                    console.log(err2);
                    res.send({ error: 1, msg: '失败' })
                } else {
                    /* db.query(mysql[1], function (err3, data3) {
                        if (err3) {
                            console.log(err3);
                            res.send({ error: 1, msg: '失败' })
                        } else {
                            console.log(data3); */
                    /* db.query(mysql2[1], function (err4, data4) {
                        if (err4) {
                            console.log(err4);
                            res.send({ error: 1, msg: '失败' })
                        } else { */
                    res.send({ msg: '成功', data: data, total: data2[0].total })
                    /*     }
                    }) */
                    /*        }
                       }) */
                    // res.send({ msg: '成功', data: [data, data3], total: data2[0].total })
                }
            })

        }
    })
})

// 渲染详情页
zxApp.get('/zx_details', function (req, res) {
    const de_sql = `SELECT a.*,b.p_package,c.c2_name,d.c1_name FROM z_sale a INNER JOIN production b ON a.p_id = b.p_id
                    LEFT JOIN class2 c on  b.p_name_id=c.c2_id
                    LEFT JOIN class1 d on  c.class1_id=d.c1_id
                    WHERE z_id=${req.query.zx_detailsId}
                    `
    db.query(de_sql, function (err, data) {
        if (err) {
            console.log(err);
            res.send({ code: 1, msg: err })
        } else {
            res.send({ code: 0, data: data })
        }
    })
})

// 删除
zxApp.post('/z_del', function (req, res) {
    console.log(req.body);
    const sql_del = `DELETE FROM z_sale WHERE z_id=${req.body.id}`
    db.query(sql_del, function (err, data) {
        if (err) {
            console.log(err);
            res.send({ code: 1, msg: '删除失败', err: err })
        } else {
            res.send({ code: 0, msg: '删除成功', data: data })
        }
    })
})

module.exports = zxApp